﻿namespace EasyWeb.Data.Sql
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.Composition;
    using System.Data;
    using System.Text;

    [Export(typeof(IEWDdlSqlGenerator))]
    [PartCreationPolicy(CreationPolicy.Shared)]
    public class EWDdlSqlGenerator : IEWDdlSqlGenerator
    {
        public virtual string Version { get; set; }

        public virtual string CreateTable(string tableName, IEnumerable<Tuple<string, DbType, bool>> columns)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");
            EWGuard.IsNotNullOrEmpty(columns, "columns");

            var result = new StringBuilder();
            result.AppendFormat("CREATE TABLE [{0}] (", tableName);
            foreach (var column in columns)
            {
                result.AppendFormat("[{0}] {1}{2},", column.Item1, TranslateColumnType(column.Item2), TranslateAllowNull(column.Item3));
            }
            result.Replace(',', ')', result.Length - 1, 1);

            return result.ToString();
        }

        public virtual string CreateColumn(string tableName, string columnName, DbType columnType, bool allowNull)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");
            EWGuard.IsNotNullOrEmpty(columnName, "columnName");
            EWGuard.IsDefinedEnum(columnType, "columnType");

            return string.Format("ALTER TABLE [{0}] ADD [{1}] {2}{3}", tableName, columnName, TranslateColumnType(columnType), TranslateAllowNull(allowNull));
        }

        public virtual string ChangeTableName(string oldTableName, string newTableName)
        {
            EWGuard.IsNotNullOrEmpty(oldTableName, "oldTableName");
            EWGuard.IsNotNullOrEmpty(newTableName, "newTableName");

            return StringComparer.OrdinalIgnoreCase.Compare(oldTableName, newTableName) == 0 ? string.Empty : string.Format("EXEC sp_rename '{0}', '{1}'", oldTableName, newTableName);
        }

        public virtual string ChangeColumnName(string tableName, string oldColumnName, string newColumnName)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");
            EWGuard.IsNotNullOrEmpty(oldColumnName, "oldColumnName");
            EWGuard.IsNotNullOrEmpty(newColumnName, "newColumnName");

            return StringComparer.OrdinalIgnoreCase.Compare(oldColumnName, newColumnName) == 0 ? string.Empty : string.Format("EXEC sp_rename '{0}.{1}', '{2}', 'COLUMN'", tableName, oldColumnName, newColumnName);
        }

        public virtual string ChangeColumnType(string tableName, string columnName, DbType columnType, bool allowNull)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");
            EWGuard.IsNotNullOrEmpty(columnName, "columnName");
            EWGuard.IsDefinedEnum(columnType, "columnType");

            return string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] {2}{3}", tableName, columnName, TranslateColumnType(columnType), TranslateAllowNull(allowNull));
        }

        public virtual string DeleteTable(string tableName)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");

            return string.Format("DROP TABLE [{0}]", tableName);
        }

        public virtual string DeleteColumn(string tableName, string columnName)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");
            EWGuard.IsNotNullOrEmpty(columnName, "columnName");

            return string.Format("ALTER TABLE [{0}] DROP COLUMN [{1}]", tableName, columnName);
        }

        public virtual string CreatePrimaryKey(string tableName, string columnName)
        {
            EWGuard.IsNotNullOrEmpty(tableName, "tableName");
            EWGuard.IsNotNullOrEmpty(columnName, "columnName");

            return string.Format("ALTER TABLE [{0}] ADD PRIMARY KEY ([{1}])", tableName, columnName);
        }

        internal static string TranslateAllowNull(bool allowNull)
        {
            return allowNull ? "" : " NOT NULL";
        }

        internal static string TranslateColumnType(DbType type)
        {
            // HACK: please update the following enumeration code if your .NET Framework or SQL Server upgraded
            switch (type)
            {
                case DbType.AnsiString:
                    return "VARCHAR(8000)";
                case DbType.AnsiStringFixedLength:
                    return "VARCHAR(8000)";
                case DbType.Binary:
                    return "VARBINARY(MAX)";
                case DbType.Boolean:
                    return "BIT";
                case DbType.Byte:
                    return "TINYINT";
                case DbType.Currency:
                    return "MONEY";
                case DbType.Date:
                    return "DATE";
                case DbType.DateTime:
                    return "DATETIME2";
                case DbType.DateTime2:
                    return "DATETIME2";
                case DbType.DateTimeOffset:
                    return "DATETIMEOFFSET";
                case DbType.Decimal:
                    return "DECIMAL(38, 5)";
                case DbType.Double:
                    return "DECIMAL(38, 5)";
                case DbType.Guid:
                    return "UNIQUEIDENTIFIER";
                case DbType.Int16:
                    return "INT";
                case DbType.Int32:
                    return "INT";
                case DbType.Int64:
                    return "BIGINT";
                // HACK: a workaround for timestamp field
                case DbType.Object:
                    return "TIMESTAMP";
                case DbType.SByte:
                    return "TINYINT";
                case DbType.Single:
                    return "DECIMAL(38, 5)";
                case DbType.String:
                    return "NVARCHAR(4000)";
                case DbType.StringFixedLength:
                    return "NVARCHAR(4000)";
                case DbType.Time:
                    return "TIME";
                case DbType.UInt16:
                    return "INT";
                case DbType.UInt32:
                    return "INT";
                case DbType.UInt64:
                    return "BIGINT";
                case DbType.VarNumeric:
                    return "DECIMAL(38, 5)";
                case DbType.Xml:
                    return "XML";
            }

            throw new NotSupportedException(string.Format("The DbType {0} is not supported to create SQL Server table column", type));
        }
    }
}
